/*
 * @(#)UpdateAlertSystemLog.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.alert.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import com.dcivision.alert.bean.UpdateAlert;
import com.dcivision.alert.bean.UpdateAlertLogAction;
import com.dcivision.alert.bean.UpdateAlertSystemLog;
import com.dcivision.alert.bean.UpdateAlertType;
import com.dcivision.dms.core.DocumentRetrievalManager;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.PermissionManager;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.framework.web.ListPersonalHomeForm;

/**
  UpdateAlertSystemLogDAObject.java
  This class is the data access bean for table "UPDATE_ALERT_SYSTEM_LOG".
  @author      Scott Tong
  @company     DCIVision Limited
  @creation date   21/08/2003
  @version     $Revision: 1.99.2.2 $
 */

public class UpdateAlertSystemLogDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.99.2.2 $";

  public static final String TABLE_NAME = "UPDATE_ALERT_SYSTEM_LOG";

  public UpdateAlertSystemLogDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("UPDATE_ALERT_TYPE_ID");
    this.vecDBColumn.add("VERSION_ID");
    this.vecDBColumn.add("REF_OBJECT_ID");
    this.vecDBColumn.add("ACTION_USER_ID");
    this.vecDBColumn.add("ACTION_DATE");
    this.vecDBColumn.add("ACTION_REPLY");
    this.vecDBColumn.add("REPLY_DATE");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }

  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, T.UPDATE_ALERT_ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A, UPDATE_ALERT_TYPE T ");
        sqlStat.append("WHERE  A.UPDATE_ALERT_TYPE_ID = T.ID AND A.ID = ?  ");
//        sqlStat.append("WHERE  A.UPDATE_ALERT_TYPE_ID = T.ID AND A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
//        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
          tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
          return (tmpUpdateAlertSystemLog);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");

        // Form the WHERE clause for filtering.
        /*
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }
        */
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
            searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
            searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
          tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
          tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
          tmpUpdateAlertSystemLog.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpUpdateAlertSystemLog);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }
      }
    }
  }

  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
          tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
          result.add(tmpUpdateAlertSystemLog);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) ( (UpdateAlertSystemLog) obj).clone();

    synchronized (dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   UPDATE_ALERT_SYSTEM_LOG(ID, UPDATE_ALERT_TYPE_ID, VERSION_ID, REF_OBJECT_ID, ACTION_USER_ID, ACTION_DATE, ACTION_REPLY, REPLY_DATE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpUpdateAlertSystemLog.getUpdateAlertTypeID());
        setPrepareStatement(preStat, 3, tmpUpdateAlertSystemLog.getVersionID());
        setPrepareStatement(preStat, 4, tmpUpdateAlertSystemLog.getRefObjectID());
        setPrepareStatement(preStat, 5, tmpUpdateAlertSystemLog.getActionUserID());
        setPrepareStatement(preStat, 6, tmpUpdateAlertSystemLog.getActionDate());
        setPrepareStatement(preStat, 7, tmpUpdateAlertSystemLog.getActionReply());
        setPrepareStatement(preStat, 8, tmpUpdateAlertSystemLog.getReplyDate());
        String recordStatus = "";
        if (GlobalConstant.RECORD_STATUS_INACTIVE.equals(tmpUpdateAlertSystemLog.getRecordStatus())){
          recordStatus = GlobalConstant.RECORD_STATUS_INACTIVE;
        } else {
          recordStatus = GlobalConstant.RECORD_STATUS_ACTIVE;
        }
        setPrepareStatement(preStat, 9, recordStatus);
        setPrepareStatement(preStat, 10, new Integer(0));
        setPrepareStatement(preStat, 11, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 12, currTime);
        setPrepareStatement(preStat, 13, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 14, currTime);
        preStat.executeUpdate();
        tmpUpdateAlertSystemLog.setID(nextID);
        tmpUpdateAlertSystemLog.setCreatorID(sessionContainer.getUserRecordID());
        tmpUpdateAlertSystemLog.setCreateDate(currTime);
        tmpUpdateAlertSystemLog.setUpdaterID(sessionContainer.getUserRecordID());
        tmpUpdateAlertSystemLog.setUpdateDate(currTime);
        tmpUpdateAlertSystemLog.setUpdateCount(new Integer(0));
        tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
        tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
        return (tmpUpdateAlertSystemLog);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) ( (UpdateAlertSystemLog) obj).clone();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("SET  UPDATE_ALERT_TYPE_ID=?, VERSION_ID=?, REF_OBJECT_ID=?, ACTION_USER_ID=?, ACTION_DATE=?, ACTION_REPLY=?, REPLY_DATE=?, RECORD_STATUS=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpUpdateAlertSystemLog.getUpdateAlertTypeID());
        setPrepareStatement(preStat, 2, tmpUpdateAlertSystemLog.getVersionID());
        setPrepareStatement(preStat, 3, tmpUpdateAlertSystemLog.getRefObjectID());
        setPrepareStatement(preStat, 4, tmpUpdateAlertSystemLog.getActionUserID());
        setPrepareStatement(preStat, 5, tmpUpdateAlertSystemLog.getActionDate());
        setPrepareStatement(preStat, 6, tmpUpdateAlertSystemLog.getActionReply());
        setPrepareStatement(preStat, 7, tmpUpdateAlertSystemLog.getReplyDate());
        String recordStatus = "";
        if (GlobalConstant.RECORD_STATUS_INACTIVE.equals(tmpUpdateAlertSystemLog.getRecordStatus())) {
          recordStatus = GlobalConstant.RECORD_STATUS_INACTIVE;
        }
        else {
          recordStatus = GlobalConstant.RECORD_STATUS_ACTIVE;
        }
        setPrepareStatement(preStat, 8, recordStatus);
        setPrepareStatement(preStat, 9, new Integer(tmpUpdateAlertSystemLog.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 10, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 11, currTime);
        setPrepareStatement(preStat, 12, tmpUpdateAlertSystemLog.getID());
        setPrepareStatement(preStat, 13, tmpUpdateAlertSystemLog.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpUpdateAlertSystemLog.setUpdaterID(sessionContainer.getUserRecordID());
          tmpUpdateAlertSystemLog.setUpdateDate(currTime);
          tmpUpdateAlertSystemLog.setUpdateCount(new Integer(tmpUpdateAlertSystemLog.getUpdateCount().intValue() + 1));
          tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
          tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
          return (tmpUpdateAlertSystemLog);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) ( (UpdateAlertSystemLog) obj).clone();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpUpdateAlertSystemLog.getID());
        setPrepareStatement(preStat, 2, tmpUpdateAlertSystemLog.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return (tmpUpdateAlertSystemLog);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog)this.oldValue;

    if (tmpUpdateAlertSystemLog != null) {
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateAlertTypeID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getVersionID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getRefObjectID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionUserID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionDate()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionReply()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getReplyDate()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateDate()));
    }

    tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) obj;
    if (tmpUpdateAlertSystemLog != null) {
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateAlertTypeID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getVersionID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getRefObjectID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionUserID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionDate()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getActionReply()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getReplyDate()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpUpdateAlertSystemLog.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/
  public synchronized void deleteByUpdateAlertTypeID(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertType tmpUpdateAlertType = (UpdateAlertType) ( (UpdateAlertType) obj).clone();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("WHERE  UPDATE_ALERT_TYPE_ID =? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpUpdateAlertType.getID());
        //setPrepareStatement(preStat, 2, tmpUpdateAlertSystemLog.getActionUserID());
        updateCnt = preStat.executeUpdate();
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  public synchronized AbstractBaseObject deleteByReply(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) ( (UpdateAlertSystemLog) obj).clone();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("WHERE  (UPDATE_ALERT_TYPE_ID = ?) AND (ACTION_USER_ID = ?) AND (ACTION_REPLY IS NOT NULL) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpUpdateAlertSystemLog.getUpdateAlertTypeID());
        setPrepareStatement(preStat, 2, tmpUpdateAlertSystemLog.getActionUserID());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
            return null;
        } else {
          return (tmpUpdateAlertSystemLog);
        }
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }
  
  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:  
   * Create Time :2006-3-28                                              *         
   * Main function: Modify for database table update_alert_type column   * 
   * "Action_type"  when invited for accept message or meeting           *
   ***********************************************************************/
  public synchronized void updateByReply(AbstractBaseObject obj) throws ApplicationException {//taohuaqing 2006-3-28
	    PreparedStatement preStat = null;
	    StringBuffer sqlStat = new StringBuffer();
	    UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) ( (UpdateAlertSystemLog) obj).clone();

	    synchronized (dbConn) {
	      try {
	        int updateCnt = 0;
	        sqlStat.append("UPDATE ");
	        sqlStat.append(" UPDATE_ALERT_TYPE ");
	        sqlStat.append(" SET ACTION_TYPE='R'");
	        sqlStat.append(" WHERE  ID = ?");
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        setPrepareStatement(preStat, 1, tmpUpdateAlertSystemLog.getUpdateAlertTypeID());
	        updateCnt = preStat.executeUpdate();
	       
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try {
	          preStat.close();
	        } catch (Exception ignore) {} finally {
	          preStat = null;
	        }
	      }
	    }
	  }
public synchronized UpdateAlertSystemLog getSystemLogBeanByID(Integer id) throws ApplicationException
{
  PreparedStatement preStat = null;
  ResultSet rs = null;
  UpdateAlertSystemLog tmpUpdateAlertSystemLog = null;
  
  StringBuffer sqlStat = new StringBuffer();
  sqlStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
  sqlStat.append("T.ACTION_TYPE, T.NEED_REPLY, T.NOTIFY_WAY, T.UPDATE_ALERT_ID, T.MESSAGE, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.DUE_DATE, T.STATUS ");
  sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID  ");
  sqlStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID  ");
  sqlStat.append("INNER JOIN MTM_UPDATE_ALERT_RECIPIENT R ON R.UPDATE_ALERT_TYPE_ID = T.ID ");
  sqlStat.append("LEFT OUTER JOIN UPDATE_ALERT_LOG_ACTION L ON L.UPDATE_ALERT_SYSTEM_LOG_ID = A.ID ");
  
  sqlStat.append("where (A.RECORD_STATUS='A') AND A.ID = ? ");
  try {
    preStat = dbConn.prepareStatement(sqlStat.toString());
    this.setPrepareStatement(preStat, 1, id);
    rs = preStat.executeQuery();
    while (rs.next()) {       
      tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
      tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
      tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
      tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
      tmpUpdateAlertSystemLog.setMessage(getResultSetString(rs, "MESSAGE"));
      tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
      tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
      tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
      tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
      tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
      tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
      tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
      tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
      tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
      tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
      tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
      tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
      tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
      tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
      tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
      tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
      tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
      tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
      tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));

      }
  } catch (Exception e) {
    log.error(e, e);
    throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
  } finally
  {
    try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
    try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
  }
  return tmpUpdateAlertSystemLog;
}
 public synchronized List getSystemLogListByUserRecordIDGroupIDListRoleIDList(Integer userRecordID, List userGroupIDList, List userRoleIDList, AbstractSearchForm searchForm) throws ApplicationException {
   //mod by greatwall 2006-10-10 for  separated from homepage logic
   ListPersonalHomeForm listHomeForm = (ListPersonalHomeForm) searchForm;
   String disableActionType = listHomeForm.getDisableActionType();
   String specifyActionType = listHomeForm.getSpecifyActionType();
   
    return getSystemLogListByUserRecordIDGroupIDListRoleIDList(userRecordID, userGroupIDList, userRoleIDList, listHomeForm, disableActionType, specifyActionType);
  }
 public List getSystemLogListByUserRecordIDGroupIDListRoleIDListForMessage(Integer userRecordID, List userGroupIDList, 
     List userRoleIDList, AbstractSearchForm searchForm, 
     String disableActionType, String specifyActionType) throws ApplicationException {

   PreparedStatement preStat = null;
   ResultSet rs = null;
   StringBuffer oriSQLStat = new StringBuffer();
   StringBuffer sqlStat = new StringBuffer();
   StringBuffer sqlStatCnt = new StringBuffer();
   
   List result = new ArrayList();
   int totalNumOfRecord = 0;
//   int rowLoopCnt = 0;
   int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
   int prvPageRowNum = 1;
   int nxtPageRowNum = startOffset;


//   int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

   PermissionManager permissionManager = sessionContainer.getPermissionManager();
   String permission = "";
   Hashtable documentRootMap = new Hashtable();
   DocumentRetrievalManager docRetrievalManager = new DocumentRetrievalManager(sessionContainer, dbConn);

   synchronized (dbConn) {
     try {
       oriSQLStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
       oriSQLStat.append("T.ACTION_TYPE, T.NEED_REPLY, T.NOTIFY_WAY, T.UPDATE_ALERT_ID, T.MESSAGE, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.DUE_DATE, T.STATUS ,T.ALERT_SUBJECT ");
       oriSQLStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID  ");
       oriSQLStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID  ");
       oriSQLStat.append("INNER JOIN MTM_UPDATE_ALERT_RECIPIENT R ON R.UPDATE_ALERT_TYPE_ID = T.ID ");
       if (!Utility.isEmpty(disableActionType)){
         oriSQLStat.append("LEFT OUTER JOIN UPDATE_ALERT_LOG_ACTION L ON L.UPDATE_ALERT_SYSTEM_LOG_ID = A.ID AND (L.ACTOR_ID = "+sessionContainer.getUserRecordID()+")  ");
       }
       oriSQLStat.append("WHERE ");
       
       oriSQLStat.append(" (T.ACTION_TYPE<>'I' OR U.OBJECT_TYPE<>'C' OR  A.CREATOR_ID<>" + userRecordID + ") AND " );
       
       if (!Utility.isEmpty(disableActionType)){
         oriSQLStat.append("(L.ID IS NULL OR L.ACTION_TYPE <>'"+disableActionType+"') AND ");
       }
       
       //--TC ADD FOR AJAX HOMEPAGE-----------------------------------------
       if(!Utility.isEmpty(specifyActionType)){
         String[] objectsAndType = specifyActionType.split("\\|");
         if(!Utility.isEmpty(objectsAndType[0]))
           oriSQLStat.append(" (U.OBJECT_TYPE IN("+objectsAndType[0]+")) AND ");
         if(objectsAndType.length>1 && !Utility.isEmpty(objectsAndType[1])){
           if(objectsAndType[1].startsWith("!"))
             oriSQLStat.append(" (T.ACTION_TYPE NOT IN( "+objectsAndType[1].substring(1)+" )) AND ");
           else
             oriSQLStat.append(" (T.ACTION_TYPE IN( "+objectsAndType[1]+" )) AND ");
           
         }
       }
       //----TC ADD END-----------------------------------------------------------
       
       oriSQLStat.append("(A.RECORD_STATUS='A') and (A.RECORD_STATUS=U.RECORD_STATUS) AND (A.ACTION_REPLY IS NULL) AND (R.RECORD_STATUS='A') ");
       oriSQLStat.append("AND ((U.OBJECT_TYPE NOT IN ('"+UpdateAlert.DOCUMENT_TYPE+"','"+UpdateAlert.AUTOMATE_CHECKIN+"')) OR EXISTS(SELECT * FROM DMS_DOCUMENT X WHERE X.ID=U.OBJECT_ID) OR EXISTS(SELECT * FROM DMS_DOCUMENT X WHERE X.ID=A.REF_OBJECT_ID AND T.ACTION_TYPE NOT IN ('"+UpdateAlert.DOCUMENT_INVITATION_CANCEL+"','"+UpdateAlert.DOCUMENT_INVITATION_EXPIRED+"','"+UpdateAlert.DOCUMENT_INVITATION_UPDATE_DUEDATE+"')))");
       oriSQLStat.append("  AND (( U.OBJECT_TYPE NOT IN ('"+UpdateAlert.WORKFLOW_TYPE+"','"+UpdateAlert.DELEGATION_TYPE+"')) OR (EXISTS(SELECT * FROM WORKFLOW_PROGRESS WP,WORKFLOW_STEP WS WHERE WP.ID=U.OBJECT_ID AND WP.WORKFLOW_STEP_ID=WS.ID AND WP.RECORD_STATUS='A' AND WS.RECORD_STATUS='A' )))  ");
       if(userRecordID!=null || 
          (userGroupIDList!=null && userGroupIDList.size()>0) ||
          (userRoleIDList!=null && userRoleIDList.size()>0)){
         oriSQLStat.append("AND (");
         if(userRecordID!=null){
           oriSQLStat.append("(R.RECIPIENT_TYPE = 'U' AND R.RECIPIENT_ID = ?)  ");
         }
         if ( (userGroupIDList!=null) && (userGroupIDList.size()>0) ){
           oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'G' AND (");
           for (int i=0; i<userGroupIDList.size(); i++){
             if(i>0) oriSQLStat.append(" OR ");
             oriSQLStat.append(" R.RECIPIENT_ID = ? ");
           }
           oriSQLStat.append(")) ");
         }
         if ( (userRoleIDList!=null) && (userRoleIDList.size()>0) ) {
           oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'R' AND (");
           for (int i=0; i<userRoleIDList.size(); i++){
             if(i>0) oriSQLStat.append(" OR ");
             oriSQLStat.append(" R.RECIPIENT_ID = ? ");
           }
           oriSQLStat.append(")) ");
         }
         oriSQLStat.append(") ");
       }
 
 oriSQLStat.append(" AND NOT EXISTS(SELECT * FROM UPDATE_ALERT_LOG_ACTION Y WHERE " +
    " Y.UPDATE_ALERT_SYSTEM_LOG_ID=A.ID AND Y.ACTION_TYPE ='"+UpdateAlertLogAction.DELETE_ACTION+"' AND Y.ACTOR_ID = ? ) ");
       // Form the ORDER clause for sorting.
       if (searchForm.isSortable()) {
         String sortAttribute = searchForm.getSortAttribute();
         if (sortAttribute.indexOf(".") < 0) {
           sortAttribute = "A." + sortAttribute;
         }
         oriSQLStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
       }

       log.debug("startOffset="+startOffset);
       /*if (pageSize < startOffset) {
         sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, startOffset + pageSize);
       } else {
         sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, pageSize);
       }*///TC DELETE IT:I DON'T KNOW WHAT THE LOGIC IS ,WHY?
       sqlStat =oriSQLStat;//this.getSelectListSQL(oriSQLStat,startOffset,pageSize);
       log.debug(sqlStat);
       preStat = dbConn.prepareStatement(oriSQLStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
       int x = 0;
 if(userRecordID!=null){
         this.setPrepareStatement(preStat, ++x, userRecordID);
       }
       if (userGroupIDList.size()!=0) {
         for (int i=0; i<userGroupIDList.size(); i++){
           this.setPrepareStatement(preStat, ++x, (Integer)userGroupIDList.get(i));
         }
       }
       if (userRoleIDList.size()!=0) {
         for (int i=0; i<userRoleIDList.size(); i++){         
           this.setPrepareStatement(preStat, ++x, (Integer)userRoleIDList.get(i));
         }
       }

       this.setPrepareStatement(preStat, ++x, userRecordID);
       
       rs = preStat.executeQuery();

//       this.positionCursor(rs, startOffset, pageSize);
//       int actualNumber = startOffset;

       while (rs.next()) {          
//         actualNumber++;
         UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
         tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
         tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
         tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
         tmpUpdateAlertSystemLog.setMessage(getResultSetString(rs, "MESSAGE"));
         tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
         tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
         tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
         tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
         tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
         tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
         tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
         tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
         tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
         tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
         tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
         tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
         tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
         tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
         tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
         tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
         tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
         tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
         tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
//         tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
//         tmpUpdateAlertSystemLog.setRowNum(startOffset++);
//         tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
//         tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
//         ++rowLoopCnt;
         result.add(tmpUpdateAlertSystemLog);          
//         if (result.size() == 0) {
//           prvPageRowNum = startOffset;
//         }
       }
       return (result);
     } catch (ApplicationException appEx) {
       throw appEx;
     } catch (SQLException sqle) {
       log.error(sqle, sqle);
       throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
     } catch (Exception e) {
       log.error(e, e);
       throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
     } finally {
       try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
       try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
     }
   }

 
 }
 public List getSystemLogListByUserRecordIDGroupIDListRoleIDList(Integer userRecordID, List userGroupIDList, 
                                      List userRoleIDList, AbstractSearchForm searchForm, 
                                      String disableActionType, String specifyActionType) throws ApplicationException {
  PreparedStatement preStat = null;
  PreparedStatement preStatCnt = null;
  ResultSet rs = null;
  ResultSet rsCnt = null;
  StringBuffer oriSQLStat = new StringBuffer();
  StringBuffer sqlStat = new StringBuffer();
  StringBuffer sqlStatCnt = new StringBuffer();
  
  List result = new ArrayList();
  int totalNumOfRecord = 0;
  int rowLoopCnt = 0;
  int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
  int prvPageRowNum = 1;
  int nxtPageRowNum = startOffset;


  int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

//// Modified by Lun on 2005-01-05
//// Get one more record to determine if next page is available
//// and if next page is available, remove the object before return
//int originalPageSize = pageSize+1;
//pageSize = pageSize+1;
//// End modified by Lun

  PermissionManager permissionManager = sessionContainer.getPermissionManager();
  String permission = "";
  Hashtable documentRootMap = new Hashtable();
  DocumentRetrievalManager docRetrievalManager = new DocumentRetrievalManager(sessionContainer, dbConn);

  synchronized (dbConn) {
    try {
      oriSQLStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
      oriSQLStat.append("T.ACTION_TYPE, T.NEED_REPLY, T.NOTIFY_WAY, T.UPDATE_ALERT_ID, T.MESSAGE, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.DUE_DATE, T.STATUS ");
      oriSQLStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID  ");
      oriSQLStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID  ");
      oriSQLStat.append("INNER JOIN MTM_UPDATE_ALERT_RECIPIENT R ON R.UPDATE_ALERT_TYPE_ID = T.ID ");
      if (!Utility.isEmpty(disableActionType)){
        oriSQLStat.append("LEFT OUTER JOIN UPDATE_ALERT_LOG_ACTION L ON L.UPDATE_ALERT_SYSTEM_LOG_ID = A.ID AND (L.ACTOR_ID = "+sessionContainer.getUserRecordID()+")  ");
      }
      oriSQLStat.append("WHERE ");
      
      

      /*
       * Update Code Start
       * Author					Jim.Zhou
       * Date						2006/06/01
       * Bug						#5572
       */
      
      oriSQLStat.append(" (T.ACTION_TYPE<>'I' OR U.OBJECT_TYPE<>'C' OR  A.CREATOR_ID<>" + userRecordID + ") AND " );
      
      /*
       * Update Code End
       */
      
      
      if (!Utility.isEmpty(disableActionType)){
        oriSQLStat.append("(L.ID IS NULL OR L.ACTION_TYPE <>'"+disableActionType+"') AND ");
      }
      
      //--TC ADD FOR AJAX HOMEPAGE-----------------------------------------
      if(!Utility.isEmpty(specifyActionType)){
        String[] objectsAndType = specifyActionType.split("\\|");
        if(!Utility.isEmpty(objectsAndType[0]))
          oriSQLStat.append(" (U.OBJECT_TYPE IN("+objectsAndType[0]+")) AND ");
        if(objectsAndType.length>1 && !Utility.isEmpty(objectsAndType[1])){
          if(objectsAndType[1].startsWith("!"))
            oriSQLStat.append(" (T.ACTION_TYPE NOT IN( "+objectsAndType[1].substring(1)+" )) AND ");
          else
            oriSQLStat.append(" (T.ACTION_TYPE IN( "+objectsAndType[1]+" )) AND ");
          
        }
      }
      //----TC ADD END-----------------------------------------------------------
      
      oriSQLStat.append("(A.RECORD_STATUS='A') and (A.RECORD_STATUS=U.RECORD_STATUS) AND (A.ACTION_REPLY IS NULL) AND (R.RECORD_STATUS='A') ");
      oriSQLStat.append("AND ((U.OBJECT_TYPE NOT IN ('"+UpdateAlert.DOCUMENT_TYPE+"','"+UpdateAlert.AUTOMATE_CHECKIN+"')) OR EXISTS(SELECT * FROM DMS_DOCUMENT X WHERE X.ID=U.OBJECT_ID) OR EXISTS(SELECT * FROM DMS_DOCUMENT X WHERE X.ID=A.REF_OBJECT_ID AND T.ACTION_TYPE NOT IN ('"+UpdateAlert.DOCUMENT_INVITATION_CANCEL+"','"+UpdateAlert.DOCUMENT_INVITATION_EXPIRED+"','"+UpdateAlert.DOCUMENT_INVITATION_UPDATE_DUEDATE+"')))");
      oriSQLStat.append("  AND (( U.OBJECT_TYPE NOT IN ('"+UpdateAlert.WORKFLOW_TYPE+"','"+UpdateAlert.DELEGATION_TYPE+"')) OR (EXISTS(SELECT * FROM WORKFLOW_PROGRESS WP,WORKFLOW_STEP WS WHERE WP.ID=U.OBJECT_ID AND WP.WORKFLOW_STEP_ID=WS.ID AND WP.RECORD_STATUS='A' AND WS.RECORD_STATUS='A' )))  ");
      if(userRecordID!=null || 
         (userGroupIDList!=null && userGroupIDList.size()>0) ||
         (userRoleIDList!=null && userRoleIDList.size()>0)){
        oriSQLStat.append("AND (");
        if(userRecordID!=null){
          oriSQLStat.append("(R.RECIPIENT_TYPE = 'U' AND R.RECIPIENT_ID = ?)  ");
        }
        if ( (userGroupIDList!=null) && (userGroupIDList.size()>0) ){
          oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'G' AND (");
          for (int i=0; i<userGroupIDList.size(); i++){
            if(i>0) oriSQLStat.append(" OR ");
            oriSQLStat.append(" R.RECIPIENT_ID = ? ");
          }
          oriSQLStat.append(")) ");
        }
        if ( (userRoleIDList!=null) && (userRoleIDList.size()>0) ) {
          oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'R' AND (");
          for (int i=0; i<userRoleIDList.size(); i++){
            if(i>0) oriSQLStat.append(" OR ");
            oriSQLStat.append(" R.RECIPIENT_ID = ? ");
          }
          oriSQLStat.append(")) ");
        }
        oriSQLStat.append(") ");
      }


oriSQLStat.append(" AND NOT EXISTS(SELECT * FROM UPDATE_ALERT_LOG_ACTION Y WHERE " +
   " Y.UPDATE_ALERT_SYSTEM_LOG_ID=A.ID AND Y.ACTION_TYPE ='"+UpdateAlertLogAction.DELETE_ACTION+"' AND Y.ACTOR_ID = ? ) ");

      // Form the ORDER clause for sorting.
      if (searchForm.isSortable()) {
        String sortAttribute = searchForm.getSortAttribute();
        if (sortAttribute.indexOf(".") < 0) {
          sortAttribute = "A." + sortAttribute;
        }
        oriSQLStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
      }

      // Retrieve the result in row basis.
      sqlStatCnt = this.getSelectCountSQLNew(oriSQLStat);
      preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
      log.debug("startOffset="+startOffset);
      log.debug("pageSize="+pageSize);
      /*if (pageSize < startOffset) {
        sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, startOffset + pageSize);
      } else {
        sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, pageSize);
      }*///TC DELETE IT:I DON'T KNOW WHAT THE LOGIC IS ,WHY?
      sqlStat =this.getSelectListSQL(oriSQLStat,startOffset,pageSize);
      log.debug(sqlStat);
      preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      int x = 0;
if(userRecordID!=null){
        this.setPrepareStatement(preStat, ++x, userRecordID);
        this.setPrepareStatement(preStatCnt, x, userRecordID);
      }
      if (userGroupIDList.size()!=0) {
        for (int i=0; i<userGroupIDList.size(); i++){
          this.setPrepareStatement(preStat, ++x, (Integer)userGroupIDList.get(i));
          this.setPrepareStatement(preStatCnt, x, (Integer)userGroupIDList.get(i));
        }
      }
      if (userRoleIDList.size()!=0) {
        for (int i=0; i<userRoleIDList.size(); i++){         
          this.setPrepareStatement(preStat, ++x, (Integer)userRoleIDList.get(i));
          this.setPrepareStatement(preStatCnt, x, (Integer)userRoleIDList.get(i));
        }
      }
      
      this.setPrepareStatement(preStat, ++x, userRecordID);
      this.setPrepareStatement(preStatCnt, x, userRecordID);
      
//        sqlStatCnt = this.getSelectCountSQL(oriSQLStat);
//        sqlStatCnt = this.getSelectCountSQL(oriSQLStat);
//        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
      rsCnt = preStatCnt.executeQuery();
      if (rsCnt.next()) {
        totalNumOfRecord = rsCnt.getInt(1);
      }
      try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
      try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

      rs = preStat.executeQuery();

      this.positionCursor(rs, startOffset, pageSize);
      int actualNumber = startOffset;

      while (rs.next() && rowLoopCnt < pageSize) {          
        actualNumber++;
        UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
        tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
        tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
        tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
        tmpUpdateAlertSystemLog.setMessage(getResultSetString(rs, "MESSAGE"));
        tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
        tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
        tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
        tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
        tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
        tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
        tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
        tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
        tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
        tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
        tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
        tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
        tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
        tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
        tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
        tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
        tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
        tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
        tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
        tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
        tmpUpdateAlertSystemLog.setRowNum(startOffset++);
        tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
        tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
        ++rowLoopCnt;
        result.add(tmpUpdateAlertSystemLog);          
        if (result.size() == 0) {
          prvPageRowNum = startOffset;
        }
      }

      boolean bNextPageAvailable = true;
      while (bNextPageAvailable) {
        startOffset = actualNumber + 1;
        int newpageSize = startOffset + pageSize + 1;
        //Del the select list sql
        //sqlStat = this.delSelectListSQL(sqlStat, startOffset, newpageSize);
        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, newpageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        x = 0;
/*
        if (updateAlertID != null) {
          for (int i = 0; i < updateAlertID.size(); i++) {
            this.setPrepareStatement(preStat, ++x, (Integer) updateAlertID.get(i));
          }
        }
*/
  if(userRecordID!=null){
          this.setPrepareStatement(preStat, ++x, userRecordID);
        }
        if (userGroupIDList.size()!=0) {
          for (int i=0; i<userGroupIDList.size(); i++){
            this.setPrepareStatement(preStat, ++x, (Integer)userGroupIDList.get(i));
          }
        }
        if (userRoleIDList.size()!=0) {
          for (int i=0; i<userRoleIDList.size(); i++){         
            this.setPrepareStatement(preStat, ++x, (Integer)userRoleIDList.get(i));
          }
        }

        this.setPrepareStatement(preStat, ++x, userRecordID);
        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, newpageSize);

        while (rs.next() && result.size() < pageSize) {        	  
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
          tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
          tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
          tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
          tmpUpdateAlertSystemLog.setRowNum(startOffset++);
          log.debug(""+tmpUpdateAlertSystemLog.getRowNum());
          tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
          tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
          //check if have next page
          tmpUpdateAlertSystemLog.setNxtPageAvailable(((totalNumOfRecord) > (startOffset  -1 + pageSize)));                            
          ++rowLoopCnt;
          result.add(tmpUpdateAlertSystemLog);        	
            if (result.size() == 0) {
              prvPageRowNum = startOffset;
            }

        }

        if (!rs.next() || result.size() == pageSize ) {
          log.debug("result.size()="+result.size());
          log.debug("pageSize="+pageSize);
          bNextPageAvailable = false;
          /* 
             Update the indicator NxtPageAvailable to last object;
             Used for special pager handling <layout:pagerNetvigator/>
          */
          if (result.size() == pageSize) {
            if (result.size()>0) {
              UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 1);
              result.remove(result.size() - 1);
              log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(true);");
              tmpUpdateAlertSystemLog.setNxtPageAvailable(true);
              result.add(tmpUpdateAlertSystemLog);
            }
          } else {
            if (result.size()>0) {
              UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 1);
              result.remove(result.size() - 1);
              log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(false);");
              tmpUpdateAlertSystemLog.setNxtPageAvailable(false);
              result.add(tmpUpdateAlertSystemLog);
            }
          }
        }
      }


//// Modified by Lun on 2005-01-05
//// Get one more record to determine if next page is available
//// and if next page is available, remove the object before return
//if ( !Utility.isEmpty(result) ) {
//log.debug(""+( (UpdateAlertSystemLog) result.get(result.size() - 1) ).getRowNum() );
//        UpdateAlertSystemLog tmpUpdateAlertSystemLog = null;
//        if ( result.size() < 2 || result.size()==originalPageSize ) {
//          // do nothing
//log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(false);");
//        }else if ( result.size() > originalPageSize ) {
//          tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 2);
//log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(true);");
//          tmpUpdateAlertSystemLog.setNxtPageAvailable(true);
//          tmpUpdateAlertSystemLog.setRowNum(tmpUpdateAlertSystemLog.getRowNum()+1);
//          result.set(result.size()-2, tmpUpdateAlertSystemLog);
//          result.remove(result.size()-1);
//        }
//}
//log.debug("Number of result in this page="+result.size());
//// End modified by Lun

      return (result);
    } catch (ApplicationException appEx) {
      throw appEx;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
    }
  }
}

  public synchronized List getSystemLogList(List updateAlertID, AbstractSearchForm searchForm) throws ApplicationException {

    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer oriSQLStat = new StringBuffer();
    StringBuffer sqlStat = new StringBuffer();
    ListPersonalHomeForm listHomeForm = (ListPersonalHomeForm) searchForm;

    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(listHomeForm.getSystemLogsCurStartRowNo());
    int prvPageRowNum = 1;
    int nxtPageRowNum = startOffset;


    int pageSize = TextUtility.parseInteger(listHomeForm.getPageOffset());

// Modified by Lun on 2005-01-05
// Get one more record to determine if next page is available
// and if next page is available, remove the object before return
int originalPageSize = pageSize;
pageSize = pageSize+1;
// End modified by Lun

    PermissionManager permissionManager = sessionContainer.getPermissionManager();
    String permission = "";
    Hashtable documentRootMap = new Hashtable();
    DocumentRetrievalManager docRetrievalManager = new DocumentRetrievalManager(sessionContainer, dbConn);

    synchronized (dbConn) {
      try {
        oriSQLStat.append("SELECT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
        oriSQLStat.append("T.ACTION_TYPE, T.NEED_REPLY, T.NOTIFY_WAY, T.UPDATE_ALERT_ID, T.MESSAGE, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.DUE_DATE, T.STATUS ");
        oriSQLStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID ");
        oriSQLStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");
        if (!Utility.isEmpty(listHomeForm.getDisableActionType())){
          oriSQLStat.append("LEFT OUTER JOIN UPDATE_ALERT_LOG_ACTION L ON L.UPDATE_ALERT_SYSTEM_LOG_ID = A.ID AND (L.ACTOR_ID = "+sessionContainer.getUserRecordID()+")  ");
        }
        oriSQLStat.append("WHERE ");
        if (!Utility.isEmpty(listHomeForm.getDisableActionType())){
          oriSQLStat.append("(L.ID IS NULL OR L.ACTION_TYPE <>'"+listHomeForm.getDisableActionType()+"') AND ");
        }

        oriSQLStat.append("(A.RECORD_STATUS='A') AND (A.ACTION_REPLY IS NULL)  ");
        if ( (updateAlertID != null) && (updateAlertID.size() > 0)) {
          oriSQLStat.append("AND (");
          for (int i = 0; i < updateAlertID.size(); i++) {
            if (i == 0) {
              oriSQLStat.append("(T.ID = ?) ");
            } else {
              oriSQLStat.append("OR (T.ID = ?) ");
            }
          }
          oriSQLStat.append(")   ");
        }

        // Form the ORDER clause for sorting.
        if (listHomeForm.isSortable()) {
          String sortAttribute = listHomeForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          oriSQLStat.append("ORDER BY " + sortAttribute + " " + listHomeForm.getSortOrder());
        }

        // Retrieve the result in row basis.
log.debug("startOffset="+startOffset);
log.debug("pageSize="+pageSize);
        if (pageSize < startOffset) {
          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, startOffset + pageSize);
        } else {
          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, pageSize);
        }
log.debug(sqlStat);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        int x = 0;
        if (updateAlertID != null) {
          for (int i = 0; i < updateAlertID.size(); i++) {
            this.setPrepareStatement(preStat, ++x, (Integer) updateAlertID.get(i));
          }
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);
        int actualNumber = startOffset;

        while (rs.next() && rowLoopCnt < pageSize) {
          actualNumber++;

              UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
              tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
              tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
              tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
              tmpUpdateAlertSystemLog.setMessage(getResultSetString(rs, "MESSAGE"));
              tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
              tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
              tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
              tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
              tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
              tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
              tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
              tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
              tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
              tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
              tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
              tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
              tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
              tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
              tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
              tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
              tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
              tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
              tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
              tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
              tmpUpdateAlertSystemLog.setRowNum(startOffset++);
log.debug(""+tmpUpdateAlertSystemLog.getRowNum());
              tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
              tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
              ++rowLoopCnt;
              result.add(tmpUpdateAlertSystemLog);
              if (result.size() == 0) {
                prvPageRowNum = startOffset;
              }
//            }
         
        }


        boolean bNextPageAvailable = true;
        while (bNextPageAvailable) {
          startOffset = actualNumber + 1;
          int newpageSize = startOffset + pageSize + 1;
          //Del the select list sql
//          sqlStat = this.delSelectListSQL(sqlStat, startOffset, newpageSize);
          // Retrieve the result in row basis.
          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, newpageSize);
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          x = 0;
          if (updateAlertID != null) {
            for (int i = 0; i < updateAlertID.size(); i++) {
              this.setPrepareStatement(preStat, ++x, (Integer) updateAlertID.get(i));
            }
          }
          rs = preStat.executeQuery();
          this.positionCursor(rs, startOffset, newpageSize);

          while (rs.next() && result.size() < pageSize) {

                UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
                tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
                tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
                tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
                tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
                tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
                tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
                tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
                tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
                tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
                tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
                tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
                tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
                tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
                tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
                tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
                tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
                tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
                tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
                tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
                tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
                tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
                tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
                tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
                tmpUpdateAlertSystemLog.setRowNum(startOffset++);                
                tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
                tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
                ++rowLoopCnt;
                result.add(tmpUpdateAlertSystemLog);
                if (result.size() == 0) {
                  prvPageRowNum = startOffset;
                }
//              }
            
          }

          if (!rs.next() || result.size() == pageSize ) {
log.debug("result.size()="+result.size());
log.debug("pageSize="+pageSize);
            bNextPageAvailable = false;
            /* Update the indicator NxtPageAvailable to last object;
               Used for special pager handling <layout:pagerNetvigator/>*/
            if (result.size() == pageSize) {
              if (result.size()>0) {
                UpdateAlertSystemLog tmpUpdateAlertSystemLog = (
                  UpdateAlertSystemLog) result.get(result.size() - 1);
                result.remove(result.size() - 1);
log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(true);");
                tmpUpdateAlertSystemLog.setNxtPageAvailable(true);
                result.add(tmpUpdateAlertSystemLog);
              }
            } else {
              if (result.size()>0) {
                UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 1);
                result.remove(result.size() - 1);
log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(false);");
                tmpUpdateAlertSystemLog.setNxtPageAvailable(false);
                result.add(tmpUpdateAlertSystemLog);
              }
            }
          }
        }


// Modified by Lun on 2005-01-05
// Get one more record to determine if next page is available
// and if next page is available, remove the object before return
if ( !Utility.isEmpty(result) ) {
log.debug(""+( (UpdateAlertSystemLog) result.get(result.size() - 1) ).getRowNum() );
        UpdateAlertSystemLog tmpUpdateAlertSystemLog = null;
        if ( result.size() < 2 || result.size()==originalPageSize ) {
          // do nothing
log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(false);");
        }else if ( result.size() > originalPageSize ) {
          tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 2);
log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(true);");
          tmpUpdateAlertSystemLog.setNxtPageAvailable(true);
          tmpUpdateAlertSystemLog.setRowNum(tmpUpdateAlertSystemLog.getRowNum());
          result.set(result.size()-2, tmpUpdateAlertSystemLog);
          result.remove(result.size()-1);
        }
}
log.debug("Number of result in this page="+result.size());
// End modified by Lun

        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   *
   * @param updateAlertID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getSystemLogList(List updateAlertID) throws ApplicationException {
    return getSystemLogListForInvite(updateAlertID, null);
  }

  public List getSystemLogListForInvite(List updateAlertID, String actionType) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List alertSystemLogList = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT L.ID, L.UPDATE_ALERT_TYPE_ID, L.VERSION_ID, L.REF_OBJECT_ID, L.ACTION_USER_ID, L.ACTION_DATE, L.ACTION_REPLY, L.REPLY_DATE, L.RECORD_STATUS, L.UPDATE_COUNT, L.CREATOR_ID, L.CREATE_DATE, L.UPDATER_ID, L.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID, U.OBJECT_TYPE, U.OBJECT_ID,  T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG L INNER JOIN UPDATE_ALERT_TYPE T ON L.UPDATE_ALERT_TYPE_ID = T.ID INNER JOIN ");
        sqlStat.append("UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");

        if ((updateAlertID != null) && (updateAlertID.size() > 0)) {
          sqlStat.append("WHERE (L.RECORD_STATUS='A') AND (L.ACTION_REPLY IS NULL)  AND (T.UPDATE_ALERT_ID = ? ");
          
          for (int i = 1; i < updateAlertID.size(); i++) {
            sqlStat.append("OR T.UPDATE_ALERT_ID = ? ");
          }
          sqlStat.append(") ");
          if(!Utility.isEmpty(actionType)&&actionType.trim().equalsIgnoreCase("I"))
          {
            sqlStat.append("  AND (T.ACTION_TYPE = 'I' ) ");
          }
        }
        sqlStat.append("ORDER BY ACTION_DATE DESC ");

        preStat = dbConn.prepareStatement(sqlStat.toString());
        if (updateAlertID != null) {
          for (int i = 0; i < updateAlertID.size(); i++) {
            this.setPrepareStatement(preStat, i + 1, (Integer) updateAlertID.get(i));
          }
        }
        rs = preStat.executeQuery();
        while (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));

          tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
          tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          alertSystemLogList.add(tmpUpdateAlertSystemLog);
        }
        return (alertSystemLogList);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized AbstractBaseObject getSystemLogByUpdateAlertIDActionType(Integer updateAlertID, String actionType) throws
    ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List alertSystemLogList = new ArrayList();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID INNER JOIN ");
        sqlStat.append("UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");
        sqlStat.append("WHERE (T.UPDATE_ALERT_ID = ?) AND (T.RECORD_STATUS = ? ) ");
        if (actionType != null) {
          sqlStat.append("AND (T.ACTION_TYPE = ?) ");
        } else {
          sqlStat.append("AND (T.ACTION_TYPE <> 'I') ");
        }

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //log.debug(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, updateAlertID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
        if (actionType != null)
          this.setPrepareStatement(preStat, 3, actionType);

        rs = preStat.executeQuery();
        if (rs.next()) {
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
    return (tmpUpdateAlertSystemLog);
  }
/**
 * 
 * @param updateAlertTypeID
 * @return
 * @throws ApplicationException
 */
  public synchronized AbstractBaseObject getInvitationSystemLogByUpdateAlertTypeID(Integer updateAlertTypeID) throws
    ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID,T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID ");
        sqlStat.append("WHERE (T.ID = ?) AND (T.RECORD_STATUS = ? ) ");
        

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //log.debug(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, updateAlertTypeID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
       

        rs = preStat.executeQuery();
        if (rs.next()) {
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
    return (tmpUpdateAlertSystemLog);
  }
  /**
   *
   * @param updateAlertSystemLog
   * @param actionReply
   * @return
   * @throws ApplicationException
   */
  public synchronized List getReplyActionByUpdateAlertID(UpdateAlertSystemLog updateAlertSystemLog, String actionReply) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT ACTION_USER_ID ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("WHERE  (UPDATE_ALERT_TYPE_ID = ?) AND ((ACTION_REPLY = ?) ");//taohuaqing 2003-3-27
        if (UpdateAlert.ACCEPT_ON_REPLY.equals(actionReply)) {
          sqlStat.append(" OR (ACTION_REPLY IS NULL) ");
        }
        sqlStat.append(") ORDER BY  ACTION_USER_ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, updateAlertSystemLog.getUpdateAlertTypeID());
        setPrepareStatement(preStat, 2, actionReply);
/*        if (UpdateAlert.ACCEPT_ON_REPLY.equals(actionReply)) {
          setPrepareStatement(preStat, 3, updateAlertSystemLog.getUpdateAlertTypeID());
        }
*/        rs = preStat.executeQuery();
        while (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          //tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          //tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          //tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          //tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          //tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          //tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          //tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          //tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          //tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          result.add(tmpUpdateAlertSystemLog);
        }
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   *
   * @param updateAlertSystemLog
   * @return
   * @throws ApplicationException
   */ 
  public synchronized AbstractBaseObject getByUpdateAlertTypeIDActionUserID(Integer updateAlertTypeID, Integer actionUserID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A ");
        sqlStat.append("WHERE  (A.UPDATE_ALERT_TYPE_ID = ?) AND (A.ACTION_USER_ID = ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, updateAlertTypeID);
        setPrepareStatement(preStat, 2, actionUserID);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          return (tmpUpdateAlertSystemLog);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get the SystemLog by UpdateAlertID,ActionAlertType and UserID
   * @param updateAlertID Update alert ID
   * @param actionAlertType Action alert type
   * @param userID User ID
   * @return UpdateAlertSystemLog
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getSystemLogByUpdateAlertIDUserID(Integer updateAlertID, String actionAlertType, Integer userID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List alertSystemLogList = new ArrayList();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = null;
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT L.ID, L.UPDATE_ALERT_TYPE_ID, L.VERSION_ID, L.REF_OBJECT_ID, L.ACTION_USER_ID, L.ACTION_DATE, L.ACTION_REPLY, L.REPLY_DATE, L.RECORD_STATUS, L.UPDATE_COUNT, L.CREATOR_ID, L.CREATE_DATE, L.UPDATER_ID, L.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG L INNER JOIN UPDATE_ALERT_TYPE T ON L.UPDATE_ALERT_TYPE_ID = T.ID INNER JOIN ");
        sqlStat.append("UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");
        sqlStat.append("WHERE (T.UPDATE_ALERT_ID = ?) AND (T.RECORD_STATUS = ? ) ");
        sqlStat.append("AND (L.ACTION_USER_ID = ?) ");
        if (actionAlertType != null)
          sqlStat.append("AND (T.ACTION_TYPE = ?) ");
        else
          sqlStat.append("AND (T.ACTION_TYPE <> 'I') ");

        preStat = dbConn.prepareStatement(sqlStat.toString());
        //log.debug(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, updateAlertID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, userID);
        if (actionAlertType != null)
          this.setPrepareStatement(preStat, 4, actionAlertType);

        rs = preStat.executeQuery();
        if (rs.next()) {
          tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));

          tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
          tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "action_type"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "update_alert_id"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          return (tmpUpdateAlertSystemLog);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Get the SystemLog by UpdateAlertID,ActionAlertType and UserID
   * @param updateAlertID Update alert ID
   * @param actionAlertType Action alert type
   * @param userID User ID
   * @return UpdateAlertSystemLog
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getSystemLogByUpdateAlertIDUserIDActionReply(Integer updateAlertID, String actionAlertType, Integer userID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List alertSystemLogList = new ArrayList();
    UpdateAlertSystemLog tmpUpdateAlertSystemLog = null;
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT L.ID, L.UPDATE_ALERT_TYPE_ID, L.VERSION_ID, L.REF_OBJECT_ID, L.ACTION_USER_ID, L.ACTION_DATE, L.ACTION_REPLY, L.REPLY_DATE, L.RECORD_STATUS, L.UPDATE_COUNT, L.CREATOR_ID, L.CREATE_DATE, L.UPDATER_ID, L.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG L INNER JOIN UPDATE_ALERT_TYPE T ON L.UPDATE_ALERT_TYPE_ID = T.ID INNER JOIN ");
        sqlStat.append("UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");
        sqlStat.append("WHERE (T.UPDATE_ALERT_ID = ?) AND (T.RECORD_STATUS = ? ) ");
        sqlStat.append("AND (L.ACTION_USER_ID = ?) ");
        if (actionAlertType != null) {
          sqlStat.append("AND (T.ACTION_TYPE = ?) ");
        } else {
          sqlStat.append("AND (T.ACTION_TYPE <> 'I') ");
        }
        sqlStat.append("AND ((L.ACTION_REPLY = 'A') OR (L.ACTION_REPLY = 'R'))");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        //log.debug(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, updateAlertID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, userID);
        if (actionAlertType != null)
          this.setPrepareStatement(preStat, 4, actionAlertType);

        rs = preStat.executeQuery();
        if (rs.next()) {
          tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));

          tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
          tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "action_type"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "update_alert_id"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          return (tmpUpdateAlertSystemLog);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get the reply result type is accept SystemLog by UpdateAlertIDList,
   * @param updateAlertID List
   * @param isAccept 
   * @return UpdateAlertSystemLog
   * @throws ApplicationException
   */
  public synchronized List getReplySystemLogList(List updateAlertID, String isAccept) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List alertSystemLogList = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT L.ID, L.UPDATE_ALERT_TYPE_ID, L.VERSION_ID, L.REF_OBJECT_ID, L.ACTION_USER_ID, L.ACTION_DATE, L.ACTION_REPLY, L.REPLY_DATE, L.RECORD_STATUS, L.UPDATE_COUNT, L.CREATOR_ID, L.CREATE_DATE, L.UPDATER_ID, L.UPDATE_DATE, ");
        sqlStat.append("T.ACTION_TYPE, T.UPDATE_ALERT_ID, U.OBJECT_TYPE, U.OBJECT_ID,  T.ALERT_TIME, T.ALERT_TIME_TYPE, T.MESSAGE, T.DUE_DATE ");
        sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG L INNER JOIN UPDATE_ALERT_TYPE T ON L.UPDATE_ALERT_TYPE_ID = T.ID INNER JOIN ");
        sqlStat.append("UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID AND U.OBJECT_TYPE = 'C' ");

        if ( (updateAlertID != null) && (updateAlertID.size() > 0)) {
          sqlStat.append("WHERE  (L.RECORD_STATUS='A') ");
          if("Y".equals(isAccept)){
            sqlStat.append("AND (L.ACTION_USER_ID = " + this.getSessionContainer().getUserRecordID().toString() + ") AND (L.ACTION_REPLY = 'A') ");
          } else {
            sqlStat.append("AND (L.ACTION_DATE <> '' ) ");//taohuaqing 2006-3-28
          }
          sqlStat.append("AND (T.UPDATE_ALERT_ID = ? ");
          for (int i = 1; i < updateAlertID.size(); i++) {
            sqlStat.append("OR T.UPDATE_ALERT_ID = ? ");
          }
          sqlStat.append(")");
        }
        sqlStat.append(" ORDER BY ACTION_DATE DESC ");
        log.debug(sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString());
        if (updateAlertID != null) {
          for (int i = 0; i < updateAlertID.size(); i++) {
            this.setPrepareStatement(preStat, i + 1, (Integer)updateAlertID.get(i));
          }
        }

        rs = preStat.executeQuery();
        while (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));

          tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
          tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          alertSystemLogList.add(tmpUpdateAlertSystemLog);
        }
        return (alertSystemLogList);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  public synchronized boolean checkTheSystemLogUpdateAlertIDIsActive(Integer id) throws ApplicationException {
    boolean checkTheSystemLogUpdateAlertIDIsActive = false;
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, T.UPDATE_ALERT_ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A, UPDATE_ALERT_TYPE T , UPDATE_ALERT U ");
        sqlStat.append("WHERE  A.UPDATE_ALERT_TYPE_ID = T.ID AND T.UPDATE_ALERT_ID  = U.ID  AND U.RECORD_STATUS = 'A' AND A.ID = ?  AND (T.DUE_DATE IS NULL  OR  T.DUE_DATE = '' OR T.DUE_DATE >= ?)");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, Utility.addDay(Utility.getCurrentTimestamp(),-1));
        rs = preStat.executeQuery();
        if (rs.next()) {
          checkTheSystemLogUpdateAlertIDIsActive = true;
        } else {
          checkTheSystemLogUpdateAlertIDIsActive = false;
        }
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
      return checkTheSystemLogUpdateAlertIDIsActive;
    }
  }
  
  /**
   * hard delete by ref object id.
   * @param refObjectID
   * @throws ApplicationException
   */
  public synchronized void hardDeleteByRefObjectID(Integer refObjectID) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG ");
        sqlStat.append("WHERE  REF_OBJECT_ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, refObjectID);
        updateCnt = preStat.executeUpdate();
        
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  public synchronized List getListByUpdateAlertTypeIDActionUserID(Integer updateAlertTypeID, Integer actionUserID) throws ApplicationException {
  	List result = new ArrayList();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A ");
        sqlStat.append("WHERE  (A.UPDATE_ALERT_TYPE_ID = ?) AND (A.ACTION_USER_ID = ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, updateAlertTypeID);
        setPrepareStatement(preStat, 2, actionUserID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
          tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
          tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
          tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
          tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
          tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
          tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
          tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
          tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
          tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUpdateAlertSystemLog.setCreatorName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          tmpUpdateAlertSystemLog.setUpdaterName(sessionContainer == null ? "" : sessionContainer.getUserFullName());
          result.add(tmpUpdateAlertSystemLog);
//          return (tmpUpdateAlertSystemLog);
        } 
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  public synchronized List getCreatUserNameListByUpdateAlertTypeID(Integer updateAlertTypeID) throws ApplicationException {
    List result = new ArrayList();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ACTION_USER_ID");
        sqlStat.append("FROM   UPDATE_ALERT_SYSTEM_LOG A ");
        sqlStat.append("WHERE  A.UPDATE_ALERT_TYPE_ID = ? AND A.RECORD_STATUS=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, updateAlertTypeID);
        setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {

          result.add(UserInfoFactory.getUserFullName(getResultSetInteger(rs, "ACTION_USER_ID")));

        } 
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
//-----king.guo---add-----start----2006-04-13
  public synchronized List getSystemLogListByUserRecordIDGroupIDListRoleIDListForDelegation(Integer userRecordID, List userGroupIDList, List userRoleIDList,List delegationIDList, AbstractSearchForm searchForm) throws ApplicationException {
	  	
	    PreparedStatement preStat = null;
	    PreparedStatement preStatCnt = null;
	    ResultSet rs = null;
	    ResultSet rsCnt = null;
	    StringBuffer oriSQLStat = new StringBuffer();
	    StringBuffer sqlStat = new StringBuffer();
	    StringBuffer sqlStatCnt = new StringBuffer();
	    ListPersonalHomeForm listHomeForm = (ListPersonalHomeForm) searchForm;

	    List result = new ArrayList();
	    int totalNumOfRecord = 0;
	    int rowLoopCnt = 0;
	    int startOffset = TextUtility.parseInteger(listHomeForm.getSystemLogsCurStartRowNo());
	    int prvPageRowNum = 1;
	    //int nxtPageRowNum = startOffset;
	    int actualNumber=startOffset;
	    //int x=0;
	    	

	    int pageSize = TextUtility.parseInteger(listHomeForm.getPageOffset());

//	 Modified by Lun on 2005-01-05
//	 Get one more record to determine if next page is available
//	 and if next page is available, remove the object before return
//	int originalPageSize = pageSize;
//	pageSize = pageSize+1;
//	 End modified by Lun

	    //PermissionManager permissionManager = sessionContainer.getPermissionManager();
	    //String permission = "";
	    //Hashtable documentRootMap = new Hashtable();
	    //DocumentRetrievalManager docRetrievalManager = new DocumentRetrievalManager(sessionContainer, dbConn);
	    List delegationList=this.getNewSystemLogListByDelegationID(delegationIDList);
	    StringBuffer idStr=new StringBuffer(); 
	    synchronized (dbConn) {
	      try {
//	    	  if(delegationList==null || delegationList.size()==0){
//	    		  return result;
//	    	  }
	    	  if(delegationList!=null && delegationList.size()>0){
	    		idStr.append(delegationList.get(0));
	    		for(int j=1;j<delegationList.size();j++){
	    			idStr.append(","+delegationList.get(j));
	    		
	    	}	    			    	    		    			    
	    		oriSQLStat.delete(0, oriSQLStat.length());	    		
		        oriSQLStat.append("SELECT DISTINCT A.ID, A.UPDATE_ALERT_TYPE_ID, A.VERSION_ID, A.REF_OBJECT_ID, A.ACTION_USER_ID, A.ACTION_DATE, A.ACTION_REPLY, A.REPLY_DATE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, ");
		        oriSQLStat.append("T.ACTION_TYPE, T.NEED_REPLY, T.NOTIFY_WAY, T.UPDATE_ALERT_ID, T.MESSAGE, U.OBJECT_TYPE, U.OBJECT_ID, T.ALERT_TIME, T.ALERT_TIME_TYPE, T.DUE_DATE, T.STATUS ");
		        oriSQLStat.append("FROM UPDATE_ALERT_SYSTEM_LOG A INNER JOIN UPDATE_ALERT_TYPE T ON A.UPDATE_ALERT_TYPE_ID = T.ID ");
		        oriSQLStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID ");
		        oriSQLStat.append("INNER JOIN MTM_UPDATE_ALERT_RECIPIENT R ON R.UPDATE_ALERT_TYPE_ID = T.ID ");
		        if (!Utility.isEmpty(listHomeForm.getDisableActionType())){
		          oriSQLStat.append("LEFT OUTER JOIN UPDATE_ALERT_LOG_ACTION L ON L.UPDATE_ALERT_SYSTEM_LOG_ID = A.ID AND (L.ACTOR_ID = "+sessionContainer.getUserRecordID()+")  ");
		        }
		        oriSQLStat.append(",user_delegation delegation WHERE A.update_alert_type_id in("+idStr.toString()+") and delegation.ID=U.object_id and delegation.record_status='"+GlobalConstant.RECORD_STATUS_ACTIVE+"' and ");
		        if (!Utility.isEmpty(listHomeForm.getDisableActionType())){
		          oriSQLStat.append("(L.ID IS NULL OR L.ACTION_TYPE <>'"+listHomeForm.getDisableActionType()+"') AND ");
		        }
		        oriSQLStat.append("(A.RECORD_STATUS='A') AND (A.ACTION_REPLY IS NULL) AND (R.RECORD_STATUS='A') ");
	
			if(userRecordID!=null || 
		           (userGroupIDList!=null && userGroupIDList.size()>0) ||
		           (userRoleIDList!=null && userRoleIDList.size()>0)){
		          oriSQLStat.append("AND (");
		          if(userRecordID!=null){
		            oriSQLStat.append("(R.RECIPIENT_TYPE = 'U' AND R.RECIPIENT_ID = ?)  ");
		          }
		          if ( (userGroupIDList!=null) && (userGroupIDList.size()>0) ){
		            oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'G' AND (");
		            for (int i=0; i<userGroupIDList.size(); i++){
		              if(i>0) oriSQLStat.append(" OR ");
		              oriSQLStat.append(" R.RECIPIENT_ID = ? ");
		            }
		            oriSQLStat.append(")) ");
		          }
		          if ( (userRoleIDList!=null) && (userRoleIDList.size()>0) ) {
		            oriSQLStat.append("OR (R.RECIPIENT_TYPE = 'R' AND (");
		            for (int i=0; i<userRoleIDList.size(); i++){
		              if(i>0) oriSQLStat.append(" OR ");
		              oriSQLStat.append(" R.RECIPIENT_ID = ? ");
		            }
		            oriSQLStat.append(")) ");
		          }
		          oriSQLStat.append(") ");
		        }
	
		        // Form the ORDER clause for sorting.
		        if (listHomeForm.isSortable()) {
		          String sortAttribute = listHomeForm.getSortAttribute();
		          if (sortAttribute.indexOf(".") < 0) {
		            sortAttribute = "A." + sortAttribute;
		          }
		          oriSQLStat.append("ORDER BY " + sortAttribute + " " + listHomeForm.getSortOrder());
		        }
	
		        // Retrieve the result in row basis.
		        sqlStatCnt = this.getSelectCountSQL(oriSQLStat);
		        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
		        log.debug("startOffset="+startOffset);
		        log.debug("pageSize="+pageSize);
		        if (pageSize < startOffset) {
		          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, startOffset + pageSize);
		        } else {
		          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, pageSize);
		        }
		        log.debug(sqlStat);  
		        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		        int x=0;
			if(userRecordID!=null){
		          this.setPrepareStatement(preStat, ++x, userRecordID);
		          this.setPrepareStatement(preStatCnt, x, userRecordID);
		        }
		        if (userGroupIDList.size()!=0) {
		          for (int i=0; i<userGroupIDList.size(); i++){
		            this.setPrepareStatement(preStat, ++x, (Integer)userGroupIDList.get(i));
		            this.setPrepareStatement(preStatCnt, x, (Integer)userGroupIDList.get(i));
		          }
		        }
		        if (userRoleIDList.size()!=0) {
		          for (int i=0; i<userRoleIDList.size(); i++){         
		            this.setPrepareStatement(preStat, ++x, (Integer)userRoleIDList.get(i));
		            this.setPrepareStatement(preStatCnt, x, (Integer)userRoleIDList.get(i));
		          }
		        }
	
		        rsCnt = preStatCnt.executeQuery();
		        if (rsCnt.next()) {
		          totalNumOfRecord = rsCnt.getInt(1);
		        }
		        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
		        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
		        
		        rs = preStat.executeQuery();
	
		        this.positionCursor(rs, startOffset, pageSize);
		        actualNumber = startOffset;
		        
		        while (rs.next() && rowLoopCnt < pageSize) {
		          actualNumber++;
		              UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
		              tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
		              tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
		              tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
		              tmpUpdateAlertSystemLog.setMessage(getResultSetString(rs, "MESSAGE"));
		              tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
		              tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
		              tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
		              tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
		              tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
		              tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
		              tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
		              tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
		              tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
		              tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
		              tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
		              tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
		              tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
		              tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
		              tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
		              tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
		              tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
		              tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
		              tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
		              tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
		              tmpUpdateAlertSystemLog.setRowNum(startOffset++);
		              tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
		              tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);		              
		              ++rowLoopCnt;
		              result.add(tmpUpdateAlertSystemLog);
		              if (result.size() == 0) {
		                prvPageRowNum = startOffset;
		              }
		        }
		      
	    	
	        boolean bNextPageAvailable = true;
	        while (bNextPageAvailable) {
	          startOffset = actualNumber + 1;
	          int newpageSize = startOffset + pageSize + 1;
	          //Del the select list sql
	          //sqlStat = this.delSelectListSQL(sqlStat, startOffset, newpageSize);
	          // Retrieve the result in row basis.
	          sqlStat = this.getSelectListSQL(oriSQLStat, startOffset, newpageSize);
	          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	          x = 0;
	/*
	          if (updateAlertID != null) {
	            for (int i = 0; i < updateAlertID.size(); i++) {
	              this.setPrepareStatement(preStat, ++x, (Integer) updateAlertID.get(i));
	            }
	          }
	*/
		  if(userRecordID!=null){
	            this.setPrepareStatement(preStat, ++x, userRecordID);
	          }
	          if (userGroupIDList.size()!=0) {
	            for (int i=0; i<userGroupIDList.size(); i++){
	              this.setPrepareStatement(preStat, ++x, (Integer)userGroupIDList.get(i));
	            }
	          }
	          if (userRoleIDList.size()!=0) {
	            for (int i=0; i<userRoleIDList.size(); i++){         
	              this.setPrepareStatement(preStat, ++x, (Integer)userRoleIDList.get(i));
	            }
	          }

	          rs = preStat.executeQuery();
	          this.positionCursor(rs, startOffset, newpageSize);

	          while (rs.next() && result.size() < pageSize) {
	                UpdateAlertSystemLog tmpUpdateAlertSystemLog = new UpdateAlertSystemLog();
	                tmpUpdateAlertSystemLog.setID(getResultSetInteger(rs, "ID"));
	                tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
	                tmpUpdateAlertSystemLog.setUpdateAlertTypeID(getResultSetInteger(rs, "UPDATE_ALERT_TYPE_ID"));
	                tmpUpdateAlertSystemLog.setVersionID(getResultSetInteger(rs, "VERSION_ID"));
	                tmpUpdateAlertSystemLog.setRefObjectID(getResultSetInteger(rs, "REF_OBJECT_ID"));
	                tmpUpdateAlertSystemLog.setActionUserID(getResultSetInteger(rs, "ACTION_USER_ID"));
	                tmpUpdateAlertSystemLog.setActionDate(getResultSetTimestamp(rs, "ACTION_DATE"));
	                tmpUpdateAlertSystemLog.setActionReply(getResultSetString(rs, "ACTION_REPLY"));
	                tmpUpdateAlertSystemLog.setReplyDate(getResultSetTimestamp(rs, "REPLY_DATE"));
	                tmpUpdateAlertSystemLog.setActionType(getResultSetString(rs, "ACTION_TYPE"));
	                tmpUpdateAlertSystemLog.setUpdateAlertID(getResultSetInteger(rs, "UPDATE_ALERT_ID"));
	                tmpUpdateAlertSystemLog.setDueDate(getResultSetTimestamp(rs, "DUE_DATE"));
	                tmpUpdateAlertSystemLog.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
	                tmpUpdateAlertSystemLog.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
	                tmpUpdateAlertSystemLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
	                tmpUpdateAlertSystemLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	                tmpUpdateAlertSystemLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	                tmpUpdateAlertSystemLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	                tmpUpdateAlertSystemLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	                tmpUpdateAlertSystemLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	                tmpUpdateAlertSystemLog.setCreatorName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getCreatorID()));
	                tmpUpdateAlertSystemLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpUpdateAlertSystemLog.getUpdaterID()));
	                tmpUpdateAlertSystemLog.setRecordCount(totalNumOfRecord);
	                tmpUpdateAlertSystemLog.setRowNum(startOffset++);
	                log.debug(""+tmpUpdateAlertSystemLog.getRowNum());
	                tmpUpdateAlertSystemLog.setPrvPageRowNum(prvPageRowNum);
	                tmpUpdateAlertSystemLog.setNxtPageRowNum(startOffset + 1);
	                //check if have next page             
	                tmpUpdateAlertSystemLog.setNxtPageAvailable(((totalNumOfRecord) > (startOffset  -1 + pageSize)));                
              
	                ++rowLoopCnt;                
	                result.add(tmpUpdateAlertSystemLog);
	                if (result.size() == 0) {
	                  prvPageRowNum = startOffset;
	                }

	          }

	          if (!rs.next() || result.size() == pageSize ) {
	            log.debug("result.size()="+result.size());
	            log.debug("pageSize="+pageSize);
	            bNextPageAvailable = false;
	            /* 
	               Update the indicator NxtPageAvailable to last object;
	               Used for special pager handling <layout:pagerNetvigator/>
	            */
	            if (result.size() == pageSize) {
	              if (result.size()>0) {
	                UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 1);
	                result.remove(result.size() - 1);
	                log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(true);");
	                tmpUpdateAlertSystemLog.setNxtPageAvailable(true);
	                result.add(tmpUpdateAlertSystemLog);
	              }
	            } else {
	              if (result.size()>0) {
	                UpdateAlertSystemLog tmpUpdateAlertSystemLog = (UpdateAlertSystemLog) result.get(result.size() - 1);
	                result.remove(result.size() - 1);
	                log.debug("tmpUpdateAlertSystemLog.setNxtPageAvailable(false);");
	                tmpUpdateAlertSystemLog.setNxtPageAvailable(false);
	                result.add(tmpUpdateAlertSystemLog);
	              }
	            }
	          }
	        }
	      }
	        return (result);
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	      }
	    }
	  }

public synchronized List getNewSystemLogListByDelegationID(List delegationIDList) throws ApplicationException {
	  PreparedStatement preStat1 = null;
	  PreparedStatement preStat2 = null;
	  ResultSet rs1 = null;
	  ResultSet rs2 = null;
	  StringBuffer sqlStat1 = new StringBuffer();
	  StringBuffer sqlStat2 = new StringBuffer();
	  StringBuffer delegationID=new StringBuffer();
	  List result = new ArrayList();
	  List updateAlertList=new ArrayList();
	  synchronized (dbConn) {
		  try {
			  if (delegationIDList == null || delegationIDList.size() == 0) {
					return null;
				}
			  for (int i = 0; i < delegationIDList.size(); i++) {
				  sqlStat1.delete(0, sqlStat1.length());				  
				  sqlStat1.append("select max(ID) ID from update_alert where object_type=? and object_id in("+delegationIDList.get(i).toString()+") and record_status=?");
				  log.debug("sqlStat: " + sqlStat1);
				  preStat1=dbConn.prepareStatement(sqlStat1.toString());
				  this.setPrepareStatement(preStat1, 1, "U");
				  this.setPrepareStatement(preStat1, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
				  rs1 = preStat1.executeQuery();
				  while(rs1.next()){				 
					  updateAlertList.add(this.getResultSetInteger(rs1, "ID"));
				  }
			  }	  			  			  			
			  for(int j=0;j<updateAlertList.size();j++){
				  sqlStat2.delete(0,sqlStat2.length());
				  sqlStat2.append("select max(ID) ID from update_alert_type where record_status=? and update_alert_id=?");
				  preStat2=dbConn.prepareStatement(sqlStat2.toString());
				  this.setPrepareStatement(preStat2, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				  this.setPrepareStatement(preStat2, 2, ((Integer)updateAlertList.get(j)).toString());
				  rs2 = preStat2.executeQuery();
				  while(rs2.next()){					  
					  result.add(this.getResultSetInteger(rs2, "ID"));
				  }
			  }
			  return result;
		  }catch (ApplicationException e) {
				throw e;
			} 
		  catch (SQLException e) {			
			  log.error(e, e);
			  throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR,
						e, e.toString());
		  }
		  catch (Exception e) {
			  log.error(e, e);
			  throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR,
						e, e.toString());
		  }finally {
				try {
					rs2.close();
					rs1.close();
				} catch (Exception ignore) {
				} finally {
					rs1 = null;
					rs2 = null;
				}
				try {
					preStat1.close();
					preStat2.close();
				} catch (Exception ignore) {
				} finally {
					preStat1 = null;
					preStat2 = null;
				}
			}
	  }
	  
}
//--king.guo---add---end--2006-04-13
}
